package com.example.victory.mapper;

import com.example.victory.entity.companyEntity.*;
import com.cgws.fintech.inner.business.customer.swagger.model.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.EnumOrdinalTypeHandler;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
import java.util.Objects;

@Mapper
@CacheConfig(cacheNames = {"inner-business-customer"})
public interface CustomerMapper {


    /**
     * 获取客户总数
     * @param accountId
     * @return
     */
    @Cacheable(key = "'customer:count:accountId:'+#p0", unless = "#result==null")
    @Select("<script> select count(DISTINCT k.KHH) from CRMII.TKHXX k\n" +
            "  left join CRMII.TKHGX uk on k.ID=uk.KHH and uk.GXLX in  (1,2,10) \n" +
            "  left join CRMII.TRYXX r on uk.RYXX=r.ID \n" +
            "  left join CRMII.TUSER u on r.DYYH=u.ID \n" +
            "  where u.zt=0 and uk.zt=0 \n" +
            "  and r.id =#{accountId} </script>")
    Integer getCustomersCount(@Param(value = "accountId") Integer accountId);


    /**
     * 根据客户类型获取客户总数
     * @param accountId
     * @return
     */
    @Cacheable(key = "'customer:count:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    @Select("<script> select count(DISTINCT k.KHH) from CRMII.TKHXX k\n" +
            "  left join CRMII.TKHGX uk on k.ID=uk.KHH and uk.GXLX =#{type} \n" +
            "  left join CRMII.TRYXX r on uk.RYXX=r.ID \n" +
            "  left join CRMII.TUSER u on r.DYYH=u.ID \n" +
            "  where u.zt=0 and uk.zt=0 \n" +
            "  and r.id =#{accountId} </script>")
    Integer getCustomersCountByType(@Param(value = "accountId") Integer accountId,@Param(value = "type") Integer type);


    /**
     * 获取客户总资产
     * @param accountId
     * @return
     */
    @Cacheable(key = "'customer:asset:accountId:'+#p0", unless = "#result==null")
    @Select("<script> SELECT TO_CHAR(SUM(NVL(SR.KHZC, 0)), '99999999999.00') KHZC\n" +
            "  FROM PUSH_CRM.TKHZHSJ SR,\n" +
            "       (SELECT DISTINCT KHH\n" +
            "          FROM CRM_APP.V_KH_RYKHGX\n" +
            "         WHERE GXLX IN (1, 2, 10)\n" +
            "           AND RYID = #{accountId} ) GX\n" +
            " WHERE SR.KHH = GX.KHH </script>")
    BigDecimal getCustomersAssetTotal(@Param(value = "accountId") Integer accountId);


    /**
     * 根据类型获取客户总资产
     * @param accountId
     * @param type
     * @return
     */
    @Cacheable(key = "'customer:asset:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    @Select("<script> SELECT TO_CHAR(SUM(NVL(SR.KHZC, 0)), '99999999999.00') KHZC\n" +
            "  FROM PUSH_CRM.TKHZHSJ SR,\n" +
            "       (SELECT DISTINCT KHH\n" +
            "          FROM CRM_APP.V_KH_RYKHGX\n" +
            "         WHERE GXLX = #{type} \n" +
            "           AND RYID = #{accountId} ) GX\n" +
            " WHERE SR.KHH = GX.KHH </script>")
    BigDecimal getCustomersAssetTotalByType(@Param(value = "accountId") Integer accountId,@Param(value = "type") Integer type);

    /**
     * 分页获取客户列表
     *
     * @param idcodeType
     * @param idcode
     * @param param
     * @return
     */
    @SelectProvider(type = SqlProvider.class, method = "getCustomersPage")
    List<CustomerPageEntity> getCustomersPage(@Param(value = "isDepartment") boolean isDepartment,@Param(value = "accountId") Integer accountId, @Param("param") ApiRequestCustomersPageData param);


    /**
     * 分页获取新开客户列表
     * @param accountId
     * @param data
     * @return
     */
    @SelectProvider(type = SqlProvider.class, method = "getNewOpenCustomersPage")
    List<CustomerPageEntity> getNewOpenCustomersPage(@Param(value = "accountId") Integer accountId, @Param("param")  ApiRequestNewOpenCustomersPageData data);



    /**
     * 获取指定客户id的客户，因为可能存在多种服务关系，所以可能返回多条记录
     *
     * @param customerId
     * @return
     */
    @Results({
            @Result(property = "gender", column = "gender", typeHandler = EnumOrdinalTypeHandler.class)
    })
    @Select("select distinct(k.KHH)  as customerId,k.KHXM as name,case sex when 2 then 0 else sex end as gender,NVL(kz.KHZC,0) as assetTotal,NVL(kz.JRCP_SZ,0) as productTotal,NVL(kz.ZJYE,0) as capitalMoney, " +
            " NVL(kz.JYN_ZGXZ,0) as incomeContribution,NVL(kz.ZC,0) as zc,NVL(kz.FZ,0) as debtTotal,NVL(kz.GPSZ,0) as marketValueTotal, " +
            " NVL(kz.PTZH_BN_JHYJL,0) as commissionRate,NVL(kz.RZRQ_WCDBBL,0) as collateralRatio,k.FXCSNL,TO_CHAR(ADD_MONTHS(TO_DATE(k.PCRQ, 'YYYYMMDD'), 24), 'YYYYMMDD') PCRQ,k.EMAIL,k.SJ as PHONE,o.Name as department,o.ID as departmentId from CRMII.TKHXX k\n" +
            "left join CRMII.TKHGX uk on k.ID=uk.KHH\n" +
            "left join CRMII.TRYXX r on uk.RYXX=r.ID\n" +
            //"left join CRMII.TUSER u on r.DYYH=u.ID \n" +
            "left join PUSH_CRM.TKHZHSJ kz on k.KHH=kz.KHH\n" +
            "left join CRMII.LBORGANIZATION o on o.id=k.YYB\n" +
            "where " +
            //" u.zt=0 and " +
            " k.KHH =#{customerId}  ")
    List<CustomerEntity> getCustomer(@Param("customerId") String customerId);


    /**
     * 获取客户的三个经理（客户经理，服务投顾，签约投顾），不存在的返回null
     *
     * @param customerId
     * @return
     */
    @Select("select k.KHH as customerId,uk.GXLX,kl.NAME as typeName,r.RYXM as manager,r.DYYH as managerId\n" +
            "from CRMII.TKHXX k\n" +
            "left join CRMII.TKHGX uk on k.ID=uk.KHH\n" +
            "left join CRMII.TKHGXLX kl on uk.GXLX=kl.IBM\n" +
            "left join CRMII.TRYXX r on uk.RYXX=r.ID\n" +
            "where uk.zt=0 and uk.GXLX in (1,2,10) and k.KHH=#{customerId}")
    List<CustomerManager> getManagersByCustomerId(String customerId);


    /**
     * 获取客户普通交易信息
     *
     * @param customerId
     * @return
     */
    @Select("SELECT KHH as customerId,PTZH_KHZC as assetTotal,  PTZH_GPSZ as marketValueTotal,PTZH_ZJYE as capitalMoney ,PTZH_JHYJL as commissionRate \n" +
            "FROM PUSH_CRM.TKHZHSJ\n" +
            "where KHH=#{customerId}")
    NormalExchangeEntity getNormalExchange(String customerId);

    /**
     * 获取客户普通交易总浮动盈亏
     *
     * @param customerId
     * @return
     */
    @Select("SELECT NVL(sum(ZXSZ - CCCB),0) FROM DSC_BAS.T_ZQYE\n" +
            "where KHH=#{customerId}")
    Double getNormalExchangeProfitLoss(String customerId);

    /**
     * 获取客户普通交易持仓信息
     *
     * @param customerId
     * @return
     */
    @Select("SELECT ZQDM as code,ZQMC as name,ZXSZ as marketValueTotal,(ZXSZ - CCCB) as profitLoss,DECODE(0,CCCB,0,(ZXSZ - CCCB)/CCCB ) as profitLossRate," +
            " ZQSL as hold, DECODE(0,ZQSL,0,CCCB/ZQSL )  as cost, DECODE(0,ZQSL,0,ZXSZ/ZQSL )  as price  " +
            "FROM DSC_BAS.T_ZQYE\n" +
            "where KHH=#{customerId} order by ZQMC")
    List<StockPosition> getNormalExchangeStockPosition(String customerId);



    /**
     * 获取客户普通交易总浮动盈亏
     *
     * @param customerId
     * @return
     */
    @Select("SELECT NVL(sum(ZXSZ - CCCB),0) FROM DSC_BAS.T_XY_ZQYE\n" +
            "where KHH=#{customerId}")
    Double getCreditExchangeProfitLoss(String customerId);

    /**
     * 获取客户信用交易持仓信息
     *
     * @param customerId
     * @return
     */
    @Select("SELECT ZQDM as code,ZQMC as name,ZXSZ as marketValueTotal,(ZXSZ - CCCB) as profitLoss, DECODE(0,CCCB,0,(ZXSZ - CCCB)/CCCB)  as profitLossRate,\n" +
            "ZQSL as hold,DECODE(0,ZQSL,0,CCCB/ZQSL ) as cost,DECODE(0,ZQSL,0, ZXSZ/ZQSL) as price\n" +
            "FROM DSC_BAS.T_XY_ZQYE\n" +
            "where KHH=#{customerId} order by ZQMC")
    List<StockPosition> getCreditExchangeStockPosition(String customerId);

    @Select("SELECT RZRQ_ZZC as assetTotal,\n" +
            "（RZRQ_ZZC-RZRQ_RZFZ-RZRQ_RQFZ-RZRQ_LXFZ) as assetNet,\n" +
            "(RZRQ_RZFZ+RZRQ_RQFZ+RZRQ_LXFZ) as debtTotal,\n" +
            "RZRQ_DQBZJYE as depositAvailable,\n" +
            "RZRQ_ZJYE as capitalMoney,\n" +
            "RZRQ_RZSXYE as financingAvailable,\n" +
            "RZRQ_SZ as marketValueTotal,\n" +
            "RZRQ_RQSXYE as stockLoanAvailable,\n" +
            "RZRQ_RZFZ as financingDebt,\n" +
            "RZRQ_RQFZ as stockLoanDebt,\n" +
            "0 as profitLoss,\n" +
            "RZRQ_LXFZ as interestDebt,\n" +
            "DECODE(100000000,RZRQ_WCDBBL,0, RZRQ_WCDBBL) as collateralRatio\n" +
            "FROM PUSH_CRM.TKHZHSJ\n" +
            "where KHH=#{customerId}")
    CreditExchange getCreditExchange(String customerId);




    /**
     * 获取客户期权交易信息
     *
     * @param customerId
     * @return
     */
    @Select("SELECT GGQQ_ZZC as assetTotal,\n" +
            "KHH as customerId,\n" +
            "GGQQ_ZJYE as capitalMoney,\n" +
            "GGQQ_ZQSZ as marketValueTotal \n" +
            "FROM PUSH_CRM.TKHZHSJ\n" +
            "where KHH=#{customerId}")
    OptionExchange getCustomerOptionExchange(String customerId);

    /**
     * 获取客户期权交易总浮动盈亏
     *
     * @param customerId
     * @return
     */
    @Select("SELECT NVL(sum(ZXSZ - CCCB),0) FROM DSC_BAS.T_SO_ZQYE\n" +
            "where KHH=#{customerId}")
    Double getCustomerOptionExchangerofitLoss(String customerId);

    /**
     * 获取客户期权交易持仓信息
     *
     * @param customerId
     * @return
     */
    @Select({"SELECT ZQDM as code,ZQMC as name,(ZXSZ - CCCB) as profitLoss,HYMC as contract," +
            " ( CASE\n" +
            "           WHEN BDBQ = 1 AND CCFX = 2 THEN\n" +
            "            '备兑策略持仓'\n" +
            "           WHEN BDBQ = 0 AND CCFX = 1 THEN\n" +
            "            '权力方'\n" +
            "           ELSE\n" +
            "            '义务方'\n" +
            "       END ) direction  ," +
            " QQLX as type ," +
            "ZQSL as hold, ZXSZ as marketValueTotal , NVL(CCCB,0) as cost \n" +
            "FROM DSC_BAS.T_SO_ZQYE where KHH=#{customerId} order by ZQMC " })
    List<OptionPosition> getOptionExchangeStockPosition(@Param(value = "customerId") String customerId);

    /**
     * 获取客户期权交易持仓信息
     *
     * @param customerId
     * @return
     */
    @Select("SELECT CPDM as code,CPJC as name,\n" +
            "CPSL as total,\n" +
            "DECODE(0,CPSL,0,ZXSZ/CPSL )  as assetValue,\n" +
            "CPSL-DJSL as availableShare,\n" +
            "ZXSZ as marketValueTotal,\n" +
            "DECODE(0,(CPSL-DJSL),0,CCCB/(CPSL-DJSL))   as callPrice,\n" +
            " NVL(ZXSZ - CCCB,0) as profitLoss \n" +
            "FROM DSC_BAS.T_FP_CPFE\n" +
            "where KHH=#{customerId} order by CPJC")
    List<OtcPositionEntity> getOtcExchangeStockPosition(String customerId);

    @Select("<script> select distinct(k.KHH) as customerId ,NVL(kz.KHZC,0) as assetTotal,NVL(kz.JRCP_SZ,0) as productTotal,NVL(kz.ZJYE,0) as capitalMoney,NVL(k.FXCSNL,0) as riskLevel,  \n" +
            " NVL(kz.JYN_ZGXZ,0) as incomeContribution,NVL(kz.GPSZ,0) as marketValueTotal,NVL(kz.JHYJL,0) as commissionRate \n" +
            " from CRMII.TKHXX k \n" +
            " left join CRMII.TKHGX uk on k.ID=uk.KHH \n" +
            " left join PUSH_CRM.TKHZHSJ kz on k.KHH=kz.KHH " +
            " left join CRMII.TRYXX r on uk.RYXX=r.ID " +
            " left join CRMII.TUSER u on r.DYYH=u.ID  \n" +
            " where  u.zt=0  and uk.GXLX in ('1','2','10') \n" +
            " and  k.KHH in <foreach collection='accountList' item='account' index='index' open='(' separator=',' close=')'>#{account}</foreach>  </script>")
    List<ApiResponseCustomerRelationInfoDataCustomers> queryCustomerGeneralInfo(@Param(value = "accountList") List<String> accountList);

    /**
     * 根据客户号获取关系类型
     * @return
     */
    @Select("<script> select uk.GXLX from CRMII.TKHGX uk left join CRMII.TKHXX k on k.ID=uk.KHH where  uk.zt=0  and uk.GXLX in ('1','2','10')  and k.KHH=#{customerId}   and  uk.RYXX=#{accountId}  </script>")
    List<Integer> queryCustomerGxlxByCustomerId(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);

    @Select("<script> SELECT CRM_APP.FUNC_LASTJYR(#{date}, #{index}) FROM DUAL  </script>")
    Integer getTradingDay(@Param(value = "date") Integer date,@Param(value = "index") int index);

    @Select("<script> SELECT TO_CHAR(TO_DATE(CRM_APP.FUNC_LASTJYR(#{date}, #{index}), 'YYYYMMDD'), 'YYYY-MM-DD') FROM DUAL  </script>")
    String getTradingDateYYYY_MM_DD(@Param(value = "date") Integer date,@Param(value = "index") int index);

    @Select("<script>  SELECT DISTINCT(KHH)  FROM DSC_STAT.T_STAT_KHYWQXKTQK WHERE KTYWQX = '112' AND ZT = '0' AND KHH=#{customerId} </script>")
    String getRzrqByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  SELECT DISTINCT(KHH) FROM DSC_STAT.T_STAT_KHYWQXKTQK WHERE KTYWQX IN ('113', '114') AND ZT = '0' AND KHH=#{customerId} </script>")
    String getSzHkByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  SELECT DISTINCT(KHH)  FROM DSC_STAT.T_STAT_KHYWQXKTQK  WHERE KTYWQX = '402' AND ZT = '0' AND KHH=#{customerId} </script>")
    String getStockOptByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>SELECT DISTINCT(KHH) FROM CRM_APP.TKHYWQX WHERE KCB = 1 AND KHH=#{customerId} </script>")
    String getShHkByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  SELECT  DISTINCT(KHH)  FROM DSC_BAS.T_GDZH WHERE JYS = 2 AND GDZT = 0 AND KHH=#{customerId} </script>")
    String getShAByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  SELECT DISTINCT(KHH)   FROM DSC_BAS.T_GDZH WHERE JYS = 4 AND GDZT = 0  AND KHH=#{customerId} </script>")
    String getShBByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  SELECT DISTINCT(KHH)  FROM DSC_BAS.T_GDZH  WHERE JYS = 1  AND GDZT = 0  AND KHH=#{customerId} </script>")
    String getSzAByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  SELECT DISTINCT(KHH)  FROM DSC_BAS.T_GDZH  WHERE JYS = 3  AND GDZT = 0 AND KHH=#{customerId} </script>")
    String getSzBByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  SELECT DISTINCT(KHH)  KHH FROM DSC_STAT.T_STAT_KHYWQXKTQK WHERE KTYWQX = '111' AND ZT = '0' AND KHH=#{customerId} </script>")
    String getCybByCustomerId(@Param(value = "customerId") String customerId);

    @Select("<script>  select * from ( SELECT MAX(KHH) KHH  FROM DSC_STAT.T_STAT_KHYWQXKTQK  WHERE KTYWQX = '902' AND ZT = '0'  GROUP BY KHH  ) a where a.KHH=#{customerId} </script>")
    String getOtcByCustomerId(@Param(value = "customerId") String customerId);

    /**
     * 新增客户列表
     * @param customerId
     * @param accountId
     * @return
     */
    @Select("<script>  SELECT  T.KHH FROM CRM_APP.TXKKH_GZ T where T.KHH=#{customerId} and T.RYID=#{accountId} </script>")
    String getTxkhhGzByCustomerIdAndId(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);

    /**
     * 删除客户列表
     * @param customerId
     * @param accountId
     */
    @Delete("<script>  DELETE  CRM_APP.TXKKH_GZ T where T.KHH=#{customerId} and T.RYID=#{accountId} </script>")
    void deleteTxkkhGzByCustomerIdAndId(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);


    /**
     * 插入客户列表
     * @param customerId
     * @param accountId
     */
    @Insert("<script>  INSERT INTO CRM_APP.TXKKH_GZ(KHH,RYID,GZRQ) VALUES(#{customerId},#{accountId},TO_CHAR(sysdate , 'YYYYMMDD')) </script>")
    void insertTxkkhGz(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);



    /**
     * 我的客户列表
     * @param customerId
     * @param accountId
     * @return
     */
    @Select("<script>  SELECT  T.KHH FROM CRM_APP.TKHLB_GZ T where T.KHH=#{customerId} and T.RYID=#{accountId} </script>")
    String getTkhlbGzByCustomerIdAndId(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);


    /**
     * 删除我的客户列表
     * @param customerId
     * @param accountId
     */
    @Delete("<script>  DELETE  CRM_APP.TKHLB_GZ T where T.KHH=#{customerId} and T.RYID=#{accountId} </script>")
    void deleteTkhlbGzByCustomerIdAndId(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);


    /**
     * 插入我的客户列表
     * @param customerId
     * @param accountId
     */
    @Insert("<script>  INSERT INTO CRM_APP.TKHLB_GZ(KHH,RYID,GZRQ) VALUES(#{customerId},#{accountId},TO_CHAR(sysdate , 'YYYYMMDD')) </script>")
    void insertTkhlbGz(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);

    /**
     * 业务机会列表
     * @param customerId
     * @param accountId
     * @return
     */
    @Select("<script>  SELECT  T.KHH FROM CRM_APP.TYWJH_GZ T where T.KHH=#{customerId} and T.RYID=#{accountId} </script>")
    String getTywjhGzByCustomerIdAndId(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);


    /**
     * 删除业务机会列表
     * @param customerId
     * @param accountId
     */
    @Delete("<script>  DELETE  CRM_APP.TYWJH_GZ T where T.KHH=#{customerId} and T.RYID=#{accountId} </script>")
    void deleteTywjhGzByCustomerIdAndId(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);

    /**
     * 插入业务机会
     * @param customerId
     * @param accountId
     */
    @Insert("<script>  INSERT INTO CRM_APP.TYWJH_GZ(KHH,RYID,GZRQ) VALUES(#{customerId},#{accountId},TO_CHAR(sysdate , 'YYYYMMDD')) </script>")
    void insertTywjhGz(@Param(value = "customerId") String customerId,@Param(value = "accountId") Integer accountId);


    @Select("<script> SELECT COUNT(1) KHZS FROM CRM_APP.V_KH_RYKHGX GX, DM.T_STAT_KHH_JBN DM,CRM_APP.V_TKHXX KH WHERE GX.GXLX = 10  AND  GX.RYID = #{accountId}  AND  DM.KHH = KH.KHH  AND GX.KHH = KH.KHH AND  KH.KHRQ BETWEEN TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE, -6), 'YYYYMMDD')) AND TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))  </script>")
    Integer getNewOpenCustomers(@Param(value = "accountId") Integer accountId);

    @Cacheable(key = "'lossWarning:bigRollOut:'+#p0+':'+#p1+':'+#p2+':'+#p3+':'+#p4", unless = "#result==null")
    @Select("SELECT *\n" +
            "  FROM (SELECT A.*, ROWNUM RN\n" +
            "          FROM (SELECT KH.KHH AS customerId,\n" +
            "                       KH.KHXM AS name,\n" +
            "                       KH.KHJB  AS custLevel,\n" +
            "                       KH.FXCSNL AS riskLevel,\n" +
            "                       KH.SJ AS phone,\n" +
            "                       (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = KH.ID AND KHGX.RYXX=#{ryid} AND GXLX = 10)||';'||\n" +
            "                       (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = KH.ID AND KHGX.RYXX=#{ryid} AND GXLX=1)||';'||\n" +
            "                       (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = KH.ID AND KHGX.RYXX=#{ryid} AND GXLX=2) AS relationType,\n" +
            "                       ZC.KHZC AS total,\n" +
            "                       SUBSTR(NR,\n" +
            "                              INSTR(NR, '转出金额') + 4, INSTR(NR, '元') - INSTR(NR, '转出金额') - 4) AS outflowMoney,\n" +
            "                       SUBSTR(NR,\n" +
            "                              INSTR(NR, '总资产的') + 4, INSTR(NR, '发生') - INSTR(NR, '总资产的') - 4) AS outflowRate,\n" +
            "                       TO_CHAR(TO_DATE(FSRQ, 'YYYYMMDD'), 'YYYY-MM-DD') AS outflowDate\n" +
            "                  FROM HIS.TMOT_SJ         SJ,\n" +
            "                       CRMII.TKHXX         KH,\n" +
            "                       PUSH_CRM.TKHZHSJ    ZC\n" +
            "                 WHERE MOTLX = '50'\n" +
            "                   AND KH.KHH = ZC.KHH\n" +
            "                   AND SJ.DXZ = KH.ID\n" +
            "                   AND EXISTS (SELECT DISTINCT KHH\n" +
            "                          FROM CRMII.TKHGX GX\n" +
            "                         WHERE GX.GXLX IN (1, 2, 10)\n" +
            "                           AND KH.ID = GX.KHH\n" +
            "                           AND GX.RYXX = #{ryid})\n" +
            "                   AND FSRQ BETWEEN #{beforeTradeDay} AND #{nowDate} \n" +
            "                 ORDER BY outflowDate DESC, customerId DESC) A\n" +
            "         ORDER BY RN)\n" +
            " WHERE RN > #{start} AND RN <= #{end} ")
    List<LossWarningEntity> getCustLossWarnBigRollOutPage(@Param("ryid") String ryid, @Param("start") Integer start, @Param("end") Integer end,
                                                          @Param("beforeTradeDay") Integer beforeTradeDay, @Param("nowDate") Integer nowDate);

    @Cacheable(key = "'lossWarning:turnToHost:'+#p0+':'+#p1+':'+#p2+':'+#p3+':'+#p4", unless = "#result==null")
    @Select(" SELECT *\n" +
            "  FROM (SELECT A.*, ROWNUM RN FROM (\n" +
            "     SELECT KH.KHH AS customerId,\n" +
            "           KH.KHXM AS name,\n" +
            "           KH.KHJB AS custLevel,\n" +
            "           KH.SJ AS phone,\n" +
            "           KH.FXCSNL AS riskLevel,\n" +
            "           (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = KH.ID AND KHGX.RYXX=#{ryid} AND GXLX = 10)||';'||\n" +
            "           (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = KH.ID AND KHGX.RYXX=#{ryid} AND GXLX=1)||';'||\n" +
            "           (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = KH.ID AND KHGX.RYXX=#{ryid} AND GXLX=2) AS relationType,\n" +
            "           ZC.KHZC AS total,\n" +
            "           SUBSTR(NR,\n" +
            "                  INSTR(NR, '股东号') + 4，INSTR(NR, '发生日期') - INSTR(NR, '股东号') - 4) as shareholders,\n" +
            "           '撤指定' as type,\n" +
            "           TO_CHAR(TO_DATE(FSRQ, 'YYYYMMDD'), 'YYYY-MM-DD') AS happenDate\n" +
            "      FROM HIS.TMOT_SJ SJ, CRMII.TKHXX KH, PUSH_CRM.TKHZHSJ ZC\n" +
            "     WHERE MOTLX = '56'\n" +
            "       AND KH.KHH = ZC.KHH\n" +
            "       AND SJ.NR LIKE '%撤指定%'\n" +
            "       AND SJ.DXZ = KH.ID\n" +
            "       AND EXISTS (SELECT DISTINCT KHH\n" +
            "                          FROM CRMII.TKHGX GX\n" +
            "                         WHERE GX.GXLX IN (1, 2, 10)\n" +
            "                           AND KH.ID = GX.KHH\n" +
            "                           AND GX.RYXX=#{ryid})\n" +
            "       AND FSRQ BETWEEN #{beforeTradeDay} AND #{nowDate} \n" +
            "        ORDER BY happenDate DESC, customerId DESC) A\n" +
            "         ORDER BY RN)\n" +
            " WHERE RN > #{start} AND RN <= #{end} ")
    List<LossWarningEntity> getCustLossWarnturnToHostPage(@Param("ryid") String ryid, @Param("start") Integer start, @Param("end") Integer end,
                                                          @Param("beforeTradeDay") Integer beforeTradeDay, @Param("nowDate") Integer nowDate);

    @SelectProvider(type = SqlProvider.class, method = "getBusChanceInfoPage")
    List<BusChanceEntity> getBusChanceInfoPage(@Param("ryid") String ryid, @Param("data") ApiRequestBusChanceData data);


    /**
     * 员工的资产
     * @param accountId
     * @param relationType
     * @return
     */
    @Cacheable(key = "'statistics:situation:captil:staff:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    @Select(" SELECT to_char(KHZS) customerTotal,to_char((CASE WHEN KHZS = 0 THEN 0  ELSE  A END))  oneCount,  TO_CHAR((CASE  WHEN KHZS = 0 THEN  0  ELSE  TRUNC(A / KHZS * 100, 2) END), '999990.00') || '%' oneRate, " +
            " to_char((CASE   WHEN KHZS = 0 THEN   0 ELSE   B END)) oneToTenCount, TO_CHAR((CASE WHEN KHZS = 0 THEN  0  ELSE TRUNC(B / KHZS * 100, 2) END), '999990.00') || '%' oneToTenRate," +
            " to_char((CASE WHEN KHZS = 0 THEN 0  ELSE C  END)) TenToFiftyCount," +
            " TO_CHAR((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(C / KHZS * 100, 2) END), '999990.00') || '%' TenToFiftyRate, to_char((CASE WHEN KHZS = 0 THEN  0 ELSE  D END)) FiftyToHunCount, " +
            " to_char((CASE WHEN KHZS = 0 THEN 0  ELSE  TRUNC(D / KHZS * 100, 2) END), '999990.00') || '%' FiftyToHunRate," +
            " to_char((CASE WHEN KHZS = 0 THEN  0 ELSE  E END)) hundredCount,TO_CHAR((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(E / KHZS * 100, 2) END), '999990.00') || '%' hundredRate " +
            "  FROM (SELECT COUNT(1) KHZS, SUM(CASE WHEN NVL(ZC.KHZC,0) < 10000 THEN 1  ELSE  0  END) A," +
            "  SUM(CASE WHEN NVL(ZC.KHZC,0) >= 10000 AND NVL(ZC.KHZC,0) < 100000 THEN 1 ELSE 0 END) B," +
            "  SUM(CASE  WHEN NVL(ZC.KHZC,0) >= 100000 AND NVL(ZC.KHZC,0) < 500000 THEN 1 ELSE 0 END) C, " +
            "  SUM(CASE WHEN NVL(ZC.KHZC,0) >= 500000 AND NVL(ZC.KHZC,0) < 1000000 THEN 1 ELSE  0 END) D, " +
            "  SUM(CASE WHEN NVL(ZC.KHZC,0) >= 1000000 THEN 1 ELSE 0 END) E FROM CRM_APP.V_KH_RYKHGX GX left JOIN PUSH_CRM.TKHZHSJ ZC ON (ZC.KHH = GX.KHH) WHERE GX.RYID = #{accountId} AND GX.GXLX = #{relationType} ) ")
    Map<String,String> getStaffCaptil(@Param(value = "accountId") Integer accountId, @Param(value = "relationType") String relationType);

    /**
     * 营业部的资产
     * @param accountId
     * @return
     */
    @Cacheable(key = "'statistics:situation:captil:deptment:accountId:'+#p0", unless = "#result==null")
    @Select("  SELECT to_char(KHZS) customerTotal,to_char((CASE WHEN KHZS = 0 THEN 0  ELSE  A END))  oneCount,  TO_CHAR((CASE  WHEN KHZS = 0 THEN  0  ELSE  TRUNC(A / KHZS * 100, 2) END), '999990.00') || '%' oneRate, " +
            "  to_char((CASE   WHEN KHZS = 0 THEN   0 ELSE   B END)) oneToTenCount, TO_CHAR((CASE WHEN KHZS = 0 THEN  0  ELSE TRUNC(B / KHZS * 100, 2) END), '999990.00') || '%' oneToTenRate,  to_char((CASE WHEN KHZS = 0 THEN 0  ELSE C  END)) TenToFiftyCount," +
            "  TO_CHAR((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(C / KHZS * 100, 2) END), '999990.00') || '%' TenToFiftyRate, to_char((CASE WHEN KHZS = 0 THEN  0 ELSE  D END)) FiftyToHunCount, to_char((CASE WHEN KHZS = 0 THEN 0  ELSE  TRUNC(D / KHZS * 100, 2) END), '999990.00') || '%' FiftyToHunRate," +
            "  to_char((CASE WHEN KHZS = 0 THEN  0 ELSE  E END)) hundredCount,TO_CHAR((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(E / KHZS * 100, 2) END), '999990.00') || '%' hundredRate FROM (SELECT COUNT(1) KHZS, SUM(CASE WHEN ZC.KHZC < 10000 THEN 1  ELSE  0  END) A, " +
            "  SUM(CASE WHEN NVL(ZC.KHZC,0) >= 10000 AND NVL(ZC.KHZC,0) < 100000 THEN 1 ELSE 0 END) B, SUM(CASE  WHEN NVL(ZC.KHZC,0) >= 100000 AND NVL(ZC.KHZC,0) < 500000 THEN 1 ELSE 0 END) C,  SUM(CASE WHEN NVL(ZC.KHZC,0) >= 500000 AND NVL(ZC.KHZC,0) < 1000000 THEN 1 ELSE  0 END) D,  SUM(CASE WHEN NVL(ZC.KHZC,0) >= 1000000 THEN 1 ELSE 0 END) E  " +
            "  FROM  CRMII.TKHXX KH left JOIN PUSH_CRM.TKHZHSJ ZC ON (ZC.KHH = KH.KHH)  WHERE KH.YYB=( SELECT ORGID FROM CRMII.TRYXX WHERE ID= #{accountId} ) ) ")
    Map<String,String> getDeptCaptil(@Param(value = "accountId") Integer accountId);


    /**
     * 员工的等级
     * @param accountId
     * @param relationType
     * @return
     */
    @Cacheable(key = "'statistics:situation:grade:staff:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    @Select(" SELECT   to_char((CASE  WHEN KHZS = 0 THEN 0 ELSE   A END)) oneGrade,  to_char((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(A / KHZS * 100, 2) END), '999990.00') || '%' oneGradeRate,  to_char((CASE WHEN KHZS = 0 THEN  0 ELSE B END)) twoGrade, " +
            "  to_char((CASE WHEN KHZS = 0 THEN 0  ELSE TRUNC(B / KHZS * 100, 2) END), '999990.00') || '%' twoGradeRate,to_char((CASE WHEN KHZS = 0 THEN 0 ELSE C END)) threeGrade," +
            "  to_char((CASE WHEN KHZS = 0 THEN  0 ELSE TRUNC(C / KHZS * 100, 2)END), '999990.00') || '%' threeGradeRate," +
            " to_char((CASE WHEN KHZS = 0 THEN  0 ELSE  D END)) fourGrade, to_char((CASE  WHEN KHZS = 0 THEN 0 ELSE TRUNC(D / KHZS * 100, 2) END), '999990.00') || '%' fourGradeRate,to_char((CASE  WHEN KHZS = 0 THEN    0 ELSE E END)) fiveGrade,  " +
            " to_char((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(E / KHZS * 100, 2)  END), '999990.00') || '%' fiveGradeRate" +
            "  FROM (SELECT COUNT(1) KHZS,SUM(CASE  WHEN JB.SX = 1 THEN 1 ELSE 0 END) A, SUM(CASE  WHEN JB.SX = 2 THEN 1 ELSE 0 END) B, SUM(CASE WHEN JB.SX = 3 THEN 1 ELSE  0 END) C, SUM(CASE WHEN JB.SX = 4 THEN 1 ELSE 0  END) D," +
            " SUM(CASE WHEN JB.SX = 5 THEN 1 ELSE 0 END) E  FROM CRMII.TKHJBDY JB INNER JOIN CRM_APP.V_KH_RYKHGX GX  ON (JB.SX = GX.KHJB) WHERE GX.RYID = #{accountId} AND GX.GXLX = #{relationType} )  ")
    Map<String,String> getStaffGrade(@Param(value = "accountId") Integer accountId, @Param(value = "relationType") String relationType);

    /**
     * 营业部的等级
     * @param accountId
     * @return
     */
    @Cacheable(key = "'statistics:situation:grade:deptment:accountId:'+#p0", unless = "#result==null")
    @Select(" SELECT   to_char((CASE  WHEN KHZS = 0 THEN 0 ELSE   A END)) oneGrade,  to_char((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(A / KHZS * 100, 2) END), '999990.00') || '%' oneGradeRate,  to_char((CASE WHEN KHZS = 0 THEN  0 ELSE B END)) twoGrade, " +
            "  to_char((CASE WHEN KHZS = 0 THEN 0  ELSE TRUNC(B / KHZS * 100, 2) END), '999990.00') || '%' twoGradeRate,to_char((CASE WHEN KHZS = 0 THEN 0 ELSE C END)) threeGrade, to_char((CASE WHEN KHZS = 0 THEN  0 ELSE TRUNC(C / KHZS * 100, 2)END), '999990.00') || '%' threeGradeRate," +
            " to_char((CASE WHEN KHZS = 0 THEN  0 ELSE  D END)) fourGrade, to_char((CASE  WHEN KHZS = 0 THEN 0 ELSE TRUNC(D / KHZS * 100, 2) END), '999990.00') || '%' fourGradeRate,to_char((CASE  WHEN KHZS = 0 THEN    0 ELSE E END)) fiveGrade,  to_char((CASE WHEN KHZS = 0 THEN 0 ELSE TRUNC(E / KHZS * 100, 2)  END), '999990.00') || '%' fiveGradeRate" +
            "  FROM (SELECT COUNT(1) KHZS,SUM(CASE  WHEN JB.SX = 1 THEN 1 ELSE 0 END) A, SUM(CASE  WHEN JB.SX = 2 THEN 1 ELSE 0 END) B, SUM(CASE WHEN JB.SX = 3 THEN 1 ELSE  0 END) C, SUM(CASE WHEN JB.SX = 4 THEN 1 ELSE 0  END) D," +
            " SUM(CASE WHEN JB.SX = 5 THEN 1 ELSE 0 END) E  FROM CRMII.TKHJBDY JB INNER JOIN  CRMII.TKHXX KH  ON (JB.SX = KH.KHJB) WHERE  KH.YYB=( SELECT ORGID FROM CRMII.TRYXX WHERE ID= #{accountId} ) )  ")
    Map<String,String> getDeptGrade(@Param(value = "accountId") Integer accountId);


    /**
     * 员工的客户分布
     * @param accountId
     * @param relationType
     * @return
     */
    @Cacheable(key = "'statistics:situation:account:staff:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    @Select(" SELECT to_char(COUNT(1)) KHZS,to_char(NVL(SUM(CASE WHEN SUBSTR(KH.KHRQ, 0, 6) = TO_CHAR(SYSDATE, 'YYYYMM') THEN 1 ELSE 0 END),0)) BYXZ,to_char(1) LX  " +
            " FROM CRM_APP.V_KH_RYKHGX GX, CRM_APP.V_TKHXX KH WHERE GX.KHH = KH.KHH   AND GX.GXLX = #{relationType}   AND GX.RYID = #{accountId} " +
            " UNION ALL SELECT to_char(COUNT(1)) KHZS, to_char(NVL(SUM(CASE WHEN SUBSTR(XYKH.KHRQ, 0, 6) = TO_CHAR(SYSDATE, 'YYYYMM') THEN 1 ELSE 0 END),0)) BYXZ, to_char(2) LX " +
            " FROM CRM_APP.V_KH_RYKHGX GX, PUSH_CRM.TXY_KHXX XYKH WHERE GX.KHH = XYKH.KHH  AND GX.GXLX = #{relationType}   AND GX.RYID = #{accountId} " +
            " UNION ALL SELECT to_char(COUNT(1)) KHZS, to_char(NVL(SUM(CASE WHEN SUBSTR(QQKH.KHRQ, 0, 6) = TO_CHAR(SYSDATE, 'YYYYMM') THEN 1  ELSE  0 END),0)) BYXZ, to_char(3) LX " +
            " FROM CRM_APP.V_KH_RYKHGX GX, DSC_BAS.T_KHXX_GGQQ QQKH WHERE GX.KHH = QQKH.KHH   AND GX.GXLX = #{relationType}   AND GX.RYID = #{accountId} ")
    List<Map<String,String>> getStaffAccount(@Param(value = "accountId") Integer accountId, @Param(value = "relationType") String relationType);

    /**
     * 营业部的客户分布
     * @param accountId
     * @return
     */
    @Cacheable(key = "'statistics:situation:account:deptment:accountId:'+#p0", unless = "#result==null")
    @Select(" SELECT to_char(COUNT(1)) KHZS,to_char(NVL(SUM(CASE WHEN SUBSTR(KH.KHRQ, 0, 6) = TO_CHAR(SYSDATE, 'YYYYMM') THEN 1 ELSE 0 END),0)) BYXZ,to_char(1) LX   " +
            " FROM CRMII.TKHXX  GX, CRM_APP.V_TKHXX KH WHERE GX.KHH = KH.KHH  AND GX.YYB=( SELECT ORGID FROM CRMII.TRYXX WHERE ID= #{accountId} ) " +
            " UNION ALL SELECT to_char(COUNT(1)) KHZS, to_char(NVL(SUM(CASE WHEN SUBSTR(XYKH.KHRQ, 0, 6) = TO_CHAR(SYSDATE, 'YYYYMM') THEN 1 ELSE 0 END),0)) BYXZ, to_char(2) LX " +
            " FROM CRMII.TKHXX GX, PUSH_CRM.TXY_KHXX XYKH WHERE GX.KHH = XYKH.KHH  AND GX.YYB=( SELECT ORGID FROM CRMII.TRYXX WHERE ID= #{accountId}) " +
            " UNION ALL SELECT to_char(COUNT(1)) KHZS, to_char(NVL(SUM(CASE WHEN SUBSTR(QQKH.KHRQ, 0, 6) = TO_CHAR(SYSDATE, 'YYYYMM') THEN 1  ELSE  0 END),0)) BYXZ, to_char(3) LX" +
            " FROM CRMII.TKHXX GX, DSC_BAS.T_KHXX_GGQQ QQKH WHERE GX.KHH = QQKH.KHH  AND GX.YYB=( SELECT ORGID FROM CRMII.TRYXX WHERE ID= #{accountId}) ")
    List<Map<String,String>> getDeptAccount(@Param(value = "accountId") Integer accountId);




    @SelectProvider(type = SqlProvider.class, method = "getNewSharestockInfo")
    List<NewSharestockEntity> getNewSharestockInfo(@Param("crmId") String crmId, @Param("searchKey") String searchKey, @Param("custLevelSearch") Integer custLevelSearch,
                                                   @Param("relationTypeSearch") Integer relationTypeSearch, @Param("startDate") String startDate,
                                                   @Param("endDate") String endDate, @Param("start") int start, @Param("end") int end);

    @SelectProvider(type = SqlProvider.class, method = "getWinnerList")
    List<WinnersListEntity> getWinnerList(@Param("crmId") String crmId, @Param("queryScope") ApiRequestWinnersListData.QueryScopeEnum queryScope,
                                          @Param("rankingType") ApiRequestWinnersListData.RankingTypeEnum rankingType, @Param("date") String date, @Param("getOneSelf") Boolean getOneSelf);

    @Cacheable(key = "'accountIncomplete:threeWayPipe:'+#p0+':'+#p1+':'+#p2", unless = "#result==null")
    @Select("SELECT *\n" +
            "  FROM (SELECT ROWNUM AS RN, A.*\n" +
            "          FROM (SELECT DISTINCT (TO_CHAR(TKHXX.KHH)) AS CUSTOMERID,\n" +
            "                                TKHXX.KHXM AS NAME,\n" +
            "                                TKHXX.KHJB AS CUSTOMERLEVEL,\n" +
            "                                TKHXX.FXCSNL AS RISKLEVEL,\n" +
            "                                (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = TKHXX.ID AND KHGX.RYXX=#{ryid} AND GXLX = 10)||';'||\n" +
            "                                (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = TKHXX.ID AND KHGX.RYXX=#{ryid} AND GXLX=1)||';'||\n" +
            "                                (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = TKHXX.ID AND KHGX.RYXX=#{ryid} AND GXLX=2) AS relationType,\n" +
            "                                TKHXX.SJ AS PHONE,\n" +
            "                                TO_CHAR(TO_DATE(TKHXX.KHRQ, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS OPENDATE,\n" +
            "                                '未绑定' threeWayPipe,\n" +
            "                                (CASE\n" +
            "                                    WHEN (SELECT COUNT(1)\n" +
            "                                            FROM DSC_BAS.T_GDZH GD\n" +
            "                                           WHERE JYS = 1\n" +
            "                                             AND GDZT = 0\n" +
            "                                             AND GD.KHH = TKHXX.KHH) > 0 THEN\n" +
            "                                     '已加挂'\n" +
            "                                    ELSE\n" +
            "                                     '未加挂'\n" +
            "                                END) shenASecuid, \n" +
            "                                (CASE\n" +
            "                                    WHEN (SELECT COUNT(1)\n" +
            "                                            FROM DSC_BAS.T_GDZH GD\n" +
            "                                           WHERE JYS = 2\n" +
            "                                             AND GDZT = 0\n" +
            "                                             AND GD.KHH = TKHXX.KHH) > 0 THEN\n" +
            "                                     '已加挂'\n" +
            "                                    ELSE\n" +
            "                                     '未加挂'\n" +
            "                                END) huASecuid \n" +
            "                  FROM CRM_APP.V_TKHXX TKHXX, CRMII.TKHGX GX\n" +
            "                 WHERE TKHXX.ID = GX.KHH\n" +
            "                 AND GX.RYXX = #{ryid}\n" +
            "                   AND GX.GXLX IN ('1', '2', '10')\n" +
            "                   AND NOT EXISTS (SELECT 1\n" +
            "                          FROM DSC_BAS.T_CGZHDY YH\n" +
            "                         WHERE QYZT IN (0, 1)\n" +
            "                           AND CGLB = 1\n" +
            "                           AND YH.KHH = TKHXX.KHH)\n" +
            "                 ORDER BY OPENDATE DESC) A\n" +
            "         WHERE ROWNUM <= #{end}) B\n" +
            " WHERE RN > #{start}")
    List<AccountIncompleteEntity> getThreeWayPipe(@Param("ryid") String ryid, @Param("start") Integer start, @Param("end") Integer end);

    @Cacheable(key = "'accountIncomplete:shareholdersCard:'+#p0+':'+#p1+':'+#p2", unless = "#result==null")
    @Select("SELECT *\n" +
            "  FROM (SELECT ROWNUM AS RN, A.*\n" +
            "          FROM (SELECT DISTINCT (TO_CHAR(TKHXX.KHH)) AS CUSTOMERID,\n" +
            "                                TKHXX.KHXM AS NAME,\n" +
            "                                TKHXX.KHJB AS CUSTOMERLEVEL,\n" +
            "                                TKHXX.FXCSNL AS RISKLEVEL,\n" +
            "                                (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = TKHXX.ID AND KHGX.RYXX=#{ryid} AND GXLX = 10)||';'||\n" +
            "                                (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = TKHXX.ID AND KHGX.RYXX=#{ryid} AND GXLX=1)||';'||\n" +
            "                                (SELECT GXLX FROM CRMII.TKHGX KHGX WHERE KHGX.KHH = TKHXX.ID AND KHGX.RYXX=#{ryid} AND GXLX=2) AS relationType,\n" +
            "                                TKHXX.SJ AS PHONE,\n" +
            "                                TO_CHAR(TO_DATE(TKHXX.KHRQ, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS OPENDATE,\n" +
            "                                (CASE\n" +
            "                                    WHEN (SELECT COUNT(1)\n" +
            "                                            FROM DSC_BAS.T_GDZH GD\n" +
            "                                           WHERE JYS = 1\n" +
            "                                             AND GDZT = 0\n" +
            "                                             AND GD.KHH = TKHXX.KHH) > 0 THEN\n" +
            "                                     '已加挂'\n" +
            "                                    ELSE\n" +
            "                                     '未加挂'\n" +
            "                                END) shenASecuid,\n" +
            "                                (CASE\n" +
            "                                    WHEN (SELECT COUNT(1)\n" +
            "                                            FROM DSC_BAS.T_GDZH GD\n" +
            "                                           WHERE JYS = 2\n" +
            "                                             AND GDZT = 0\n" +
            "                                             AND GD.KHH = TKHXX.KHH) > 0 THEN\n" +
            "                                     '已加挂'\n" +
            "                                    ELSE\n" +
            "                                     '未加挂'\n" +
            "                                END) huASecuid \n" +
            "                  FROM CRM_APP.V_TKHXX TKHXX, CRMII.TKHGX GX\n" +
            "                 WHERE TKHXX.ID = GX.KHH\n" +
            "                 AND GX.RYXX = #{ryid}\n" +
            "                   AND GX.GXLX IN ('1', '2', '10')\n" +
            "                   AND TKHXX.KHH NOT IN (SELECT KHH\n" +
            "                                           FROM DSC_BAS.T_GDZH GD\n" +
            "                                          WHERE JYS = 1\n" +
            "                                            AND GDZT = 0\n" +
            "                                            AND GD.KHH = TKHXX.KHH\n" +
            "                                            AND KHH IN (SELECT KHH\n" +
            "                                                          FROM DSC_BAS.T_GDZH GDZH\n" +
            "                                                         WHERE JYS = 2\n" +
            "                                                           AND GDZT = 0))\n" +
            "                 ORDER BY OPENDATE DESC) A\n" +
            "         WHERE ROWNUM <= #{end}) B\n" +
            " WHERE RN > #{start}")
    List<AccountIncompleteEntity> getShareholdersCard(@Param("ryid") String ryid, @Param("start") Integer start, @Param("end") Integer end);

    @Cacheable(key = "'DepOverviewEntity:yybid:'+#p0", unless = "#result==null")
    @Select("SELECT COUNT(1) customerCount, TRIM(TO_CHAR(NVL(SUM(KHZC), 0), '999999999990.00')) assetTotal\n" +
            "  FROM CRMII.TKHXX TKHXX, PUSH_CRM.TKHZHSJ B1\n" +
            " WHERE TKHXX.KHH = B1.KHH(+) and tkhxx.yyb = (SELECT orgid FROM CRMII.tryxx WHERE ID = #{ryid})")
    DepOverviewEntity getDepOverview(Integer ryid);

    @Select("SELECT \n" +
            "       to_char(SUM(JBGZ),'FM999,999,999,999,990.00') jbgz,\n" +    // --基本工资,
            "       to_char(SUM(GDGZ),'FM999,999,999,999,990.00') gdgz,\n" +    // --固定工资,
            "       to_char(SUM(FDGZ),'FM999,999,999,999,990.00') fdgz,\n" +    // --浮动工资,
            "       to_char(SUM(GZTZX),'FM999,999,999,999,990.00') gztzx,\n" +  // --工资调整项,
            "       to_char(SUM(GLKHFWTC),'FM999,999,999,999,990.00') glkhfwtc,\n" +    // --管理客户服务提成,
            "       to_char(SUM(GGQQ),'FM999,999,999,999,990.00') qqyjtc,\n" +  // --期权佣金提成,
            "       to_char(SUM(YXFWTCB),'FM999,999,999,999,990.00') yxfwtcb, \n" +     //--营销服务提成包,
            "       to_char(SUM(YXYJTC),'FM999,999,999,999,990.00') yxyjtc,\n" +    // --营销佣金提成,
            "       to_char(SUM(TGZLSYTC),'FM999,999,999,999,990.00') tgfwzlsy, \n" +   //--投顾服务增量收益,
            "       to_char(SUM(KHJ),'FM999,999,999,999,990.00') khj,\n" +  // --开户奖,
            "       to_char(SUM(KHDF),'FM999,999,999,999,990.00') khdf,\n" +    // --考核得分,
            "       to_char(SUM(GLJT),'FM999,999,999,999,990.00') gljt,\n" +    // --管理津贴,
            "       to_char(SUM(SBKK),'FM999,999,999,999,990.00') sbkk, \n" +   //--社保扣款,
            "       to_char(SUM(GJJ),'FM999,999,999,999,99.00') gjjkk,\n" +    // --公积金扣款,
            "       to_char(SUM(QYNJ),'FM999,999,999,999,990.00') qynjkk,\n" +  // --企业年金扣款,
            "       to_char(SUM(QTKK),'FM999,999,999,999,990.00') qtkk,\n" +    // --其他扣款,
            "       to_char(SUM(YKHJ),'FM999,999,999,999,990.00') ykhj,\n" +    // --应扣合计,
            "       to_char(SUM(YSSD),'FM999,999,999,999,990.00') yssd, \n" +   // --应税所得,
            "       to_char(SUM(GRSDS),'FM999,999,999,999,990.00') grsds, \n" +     //--个人所得税,
            "       to_char(SUM(FXJ),'FM999,999,999,999,990.00') fxjkjse, \n" +     //--风险金扣缴数额,
            "       to_char(SUM(SHTZX),'FM999,999,999,999,990.00') shtzx, \n" +     //--税后调整项,
            "       to_char(SUM(YFGZ),'FM999,999,999,999,990.00') yfgz,\n" +    // --应发工资,
            "       to_char(SUM(SFGZ),'FM999,999,999,999,990.00') sfgz \n" +    //--实发工资
            "  FROM (SELECT BD.YF,\n" +
            "               A.YYB YYB,\n" +
            "               A.RYID ID,\n" +
            "               A.RYBH,\n" +
            "               A.RYXM XM,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 1001 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) JBGZ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 2002 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) GDGZ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 2006 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) FDGZBZ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 2001 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) FDGZ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 2003 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) GLKHFWTC,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 2004 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) TGZLSYTC,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 2005 THEN\n" +
            "                        TRUNC(SSJE, 2)\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) KHDF,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 1002 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) YXYJTC,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 1006 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) YXFWTCB,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 1007 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) GZTZX,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 1004 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) GLJT,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 1050 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) YFGZ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 3004 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) KHJ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 3006 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) FXJ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4002 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) SBKK,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4003 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) GJJ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4004 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) QYNJ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4005 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) QTKK,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4006 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) YKHJ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4007 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) GRSDS,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4008 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) YSSD,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 4010 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) SHTZX,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 5001 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) SFGZ,\n" +
            "               SUM(CASE\n" +
            "                       WHEN BD.XCXM = 1008 THEN\n" +
            "                        SSJE\n" +
            "                       ELSE\n" +
            "                        0\n" +
            "                   END) GGQQ\n" +
            "          FROM CRM_XC.TRYXX_LS A,\n" +
            "               (SELECT *\n" +
            "                  FROM CRM_XC.TRYXCBD\n" +
            "                 WHERE XCXM IN (1001, \n" +    //--基本工资
            "                                2002, \n" +    //--固定工资
            "                                2006, \n" +    //--浮动工资标准
            "                                2001, \n" +    //--浮动工资
            "                                2003, \n" +    //--管理客户服务提成
            "                                2004, \n" +    //--投顾服务增量收益
            "                                2005, \n" +    //--考核得分
            "                                1002, \n" +    //--营销佣金提成
            "                                1006, \n" +    //--营销服务提成包
            "                                1007, \n" +    //--工资调整项
            "                                1008, \n" +    //--期权佣金提成
            "                                1004, \n" +    //--管理津贴
            "                                1050, \n" +    //--应发工资
            "                                3004, \n" +    //--开户奖
            "                                3006, \n" +    //--风险金
            "                                4002, \n" +    //--社保扣款
            "                                4003, \n" +    //--公积金扣款
            "                                4004, \n" +    //--企业年金扣款
            "                                4005, \n" +    //--其他扣款
            "                                4010, \n" +    //--税后调整项
            "                                4006, \n" +    //--应扣合计
            "                                4007, \n" +    //--个人所得税
            "                                4008, \n" +    //--应税所得
            "                                5001 \n" +     //--实发工资
            "                                )) BD\n" +
            "         WHERE A.YF = BD.YF\n" +
            "           AND A.LB <> 7\n" +
            "           AND A.RYID = BD.RYID(+)\n" +
            "           AND BD.YF = #{month}\n" +
            "           AND A.RYID = #{ryid}\n" +
            "         GROUP BY A.RYID, A.RYBH, A.RYXM, A.YYB, BD.YF) T,\n" +
            "       CRMII.LBORGANIZATION LB\n" +
            " WHERE LB.ID = T.YYB\n" +
            " GROUP BY YF, T.YYB, LB.NAME, T.ID, RYBH, XM\n" +
            " ORDER BY T.YF, T.YYB,T.ID")
    SalaryStatusEntity getSalaryStatus(@Param("ryid") Integer ryid, @Param("month") Integer month);

    @Select("SELECT ZB.KHRY,\n" +
            "       NVL((SELECT ZBZ\n" +
            "          FROM XC_DATA.TKHJLZBTJ T\n" +
            "         WHERE T.KHJL = RY.JSRY\n" +
            "           AND T.MON = ZB.YF\n" +
            "           AND T.ZBDM = 'RW_NXKYXH_RY'),0) newValidAccountTask,\n" +  //新开净增有效户任务量
            "       NVL(SUM(CASE\n" +
            "               WHEN ZB.ZBDM = 'NXKYXH_RY' THEN\n" +
            "                ZBZ\n" +
            "               ELSE\n" +
            "                0\n" +
            "           END),0) newValidAccount,\n" +  //新开净增有效户
            "       NVL((SELECT ZBZ\n" +
            "          FROM XC_DATA.TKHJLZBTJ T\n" +
            "         WHERE T.KHJL = RY.JSRY\n" +
            "           AND T.MON = ZB.YF\n" +
            "           AND T.ZBDM = 'RW_NXKHJZZC_RY'),0) newIncreaseAssetTask,\n" +    //新开净增资产任务量
            "       NVL(SUM(CASE\n" +
            "               WHEN ZB.ZBDM = 'NXKHJZZC_RY' THEN\n" +
            "                ZBZ\n" +
            "               ELSE\n" +
            "                0\n" +
            "           END),0) newIncreaseAsset,\n" +  //新开净增资产
            "       NVL((SELECT T.ZBZ\n" +
            "          FROM XC_DATA.TKHJLZBTJ T\n" +
            "         WHERE T.KHJL = RY.JSRY\n" +
            "           AND T.MON = ZB.YF\n" +
            "           AND T.ZBDM = 'RW_YJRCPJYL_RY'),0) monthSalesTask,\n" +    //月金融产品销售量任务量
            "       NVL(SUM(CASE\n" +
            "               WHEN ZB.ZBDM = 'YJRCPJYL_RY' THEN\n" +
            "                ZBZ\n" +
            "               ELSE\n" +
            "                0\n" +
            "           END),0) monthSales,\n" +    //月金融产品销售量
            "       NVL((SELECT T.ZBZ\n" +
            "          FROM XC_DATA.TKHJLZBTJ T\n" +
            "         WHERE T.KHJL = RY.JSRY\n" +
            "           AND T.MON = ZB.YF\n" +
            "           AND T.ZBDM = 'RW_NJRCPJYL_RY'),0) annualSalesTask,\n" +    //年金融产品销售量任务量
            "       NVL(SUM(CASE\n" +
            "               WHEN ZB.ZBDM = 'NJRCPJYL_RY' THEN\n" +
            "                ZBZ\n" +
            "               ELSE\n" +
            "                0\n" +
            "           END),0) annualSales\n" +       //年金融产品销售量
            "  FROM XC_DATA.TJXKHJG ZB, CRM_XC.TRYXX_LS RY\n" +
            " WHERE ZB.YF = #{month}\n" +
            "   AND ZB.YF = RY.YF\n" +
            "   AND ZB.KHRY = RY.JSRY\n" +
            "   AND RY.RYID = #{ryid}\n" +
            "   AND ZB.BMLB = 2\n" +
            " GROUP BY ZB.KHRY, ZB.YF, RY.JSRY")
    TargetStatusEntity getTargetStatus(@Param("ryid") Integer ryid, @Param("month") Integer month);

    @Select("SELECT DY.NOTE assessProject,\n" +
            "       NVL(T2.ZBZ, 0) targetValue,\n" +
            "       NVL(T.KPQZ * T.KHDF, 0) score\n" +
            "  FROM CRMII.TKHZBDMDYRY DY\n" +
            "  LEFT JOIN (SELECT ZB.ZBDM, ZB.ZBZ, ZB.KPQZ, ZB.KHDF\n" +
            "               FROM XC_DATA.TJXKHJG ZB, CRM_XC.TRYXX RY\n" +
            "              WHERE ZB.KHRY = RY.JSRY\n" +
            "                AND ZB.YF = #{month}\n" +
            "                AND ZB.BMLB = 2\n" +
            "                AND RY.ID = #{ryid}) T\n" +
            "    ON T.ZBDM(+) = DY.CATEGORY\n" +
            "  LEFT JOIN (SELECT ZB.ZBDM, ZB.ZBZ\n" +
            "               FROM XC_DATA.TKHJLZBTJ ZB, CRM_XC.TRYXX RY\n" +
            "              WHERE ZB.KHJL = RY.JSRY\n" +
            "                AND ZB.MON = #{month}\n" +
            "                AND ZB.BMLB = 2\n" +
            "                AND RY.ID = #{ryid}) T2\n" +
            "    ON T2.ZBDM = 'RW_' || DY.CATEGORY\n" +
            " ORDER BY DY.IBM")
    List<CoreAssessItems> getCoreAssessmentItems(@Param("ryid") Integer ryid, @Param("month") Integer month);

    @Select("SELECT T.ZBMC assessProject, ROUND(NVL(ZB.ZBZ, 0), 2) targetValue\n" +
            "  FROM CRMII.TXTZBDM T\n" +
            "  LEFT JOIN XC_DATA.TKHJLZBTJ ZB\n" +
            "    ON (ZB.KHJL = #{ryid} AND ZB.MON = #{month} AND ZB.ZBDM = T.ZBDM)\n" +
            " WHERE T.ZBLB = 5\n" +
            "   AND T.ZBDM LIKE '%_RY'")
    List<AssessAddOrSubPoint> getAssessmentAddOrSubPoint(@Param("ryid") Integer ryid, @Param("month") Integer month);

    @Cacheable(key = "'captial:count:accountId:'+#p0", unless = "#result==null")
    @Select(" SELECT to_char(count(1)) as customerTotal,to_char(NVL(SUM( NVL(ZC.KHZC,0)),0)) as total  " +
            " FROM  CRMII.TKHXX KH left JOIN PUSH_CRM.TKHZHSJ ZC ON (ZC.KHH = KH.KHH)  WHERE KH.YYB=( SELECT ORGID FROM CRMII.TRYXX WHERE ID=#{accountId} )  ")
    Map<String,String> getDeptCustomersTotal(@Param("accountId") Integer accountId);

    @SelectProvider(type = SqlProvider.class, method = "getContractExpire")
    List<ContractExpireEntity> getContractExpire(@Param("ryid") Integer ryid, @Param("param") ApiRequestContractExpireData param, @Param("startDate") String startDate, @Param("endDate") String endDate);

    @Select("  select  jyr  from HIS.TXTJYR   where zrr=#{dateStr}  ")
    String getCrmMarketDay(@Param(value = "dateStr") String dateStr);


    /**
     * 复杂sql生成类
     */
    @Slf4j
    class SqlProvider {

        public String getContractExpire(@Param("ryid") Integer ryid, @Param("param") ApiRequestContractExpireData param, @Param("startDate") String startDate, @Param("endDate") String endDate) {
            Integer index = param.getIndex();
            Integer pageSize = param.getPageSize();
            if (index == null) index = 0;
            if (pageSize == null) pageSize = 20;
            Integer start = index * pageSize;
            Integer end = (index + 1) * pageSize;

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT *\n" +
                    "  FROM (SELECT A.*, ROWNUM RN  FROM (\n" +
                    "SELECT KH.KHH customerId,\n" +
                    "       KH.KHXM name,\n" +
                    "       KH.SJ phone,\n" +
                    "       QY.QYMS contractPattern, \n" +  // --1签约投顾  3签约产品
                    "       QY.SFMS chargePattern, \n" +  // -- 1免费 2账户管理费 3差额佣金费率 4定额收费 5银行卡扣费
                    "       TO_CHAR(TO_DATE(QY.SXRQ, 'YYYYMMDD'), 'YYYY.MM.DD') effectiveDate,\n" +
                    "       TO_CHAR(TO_DATE(QY.JZRQ, 'YYYYMMDD'), 'YYYY.MM.DD') expireDate\n" +
                    "  FROM CRMII.TKHXX KH, CRMII.TTZGW_QYXY QY\n" +
                    " WHERE QY.JZRQ between #{startDate} and #{endDate} \n" +   // --i个月内到期的
                    "   AND QY.KHH = KH.ID\n" +
                    "   AND QY.XYJZRQ IS NOT NULL\n" +
                    "   AND QY.QYZT = 0\n" );
            if (!StringUtils.isEmpty(param.getSearchKey())) {
                sql.append(" AND (KH.KHH LIKE CONCAT(CONCAT('%', #{param.searchKey}), '%')\n" +
                        "                         OR KH.KHXM LIKE CONCAT(CONCAT('%', #{param.searchKey}), '%')\n" +
                        "                         OR KH.SJ LIKE CONCAT(CONCAT('%', #{param.searchKey}), '%'))");
            }
            if (param.getCustLevelSearch() != null) sql.append(" AND KH.KHJB = #{param.custLevelSearch}");
            if (param.getContractPatternSearch() != null) sql.append(" AND QY.QYMS = #{param.contractPatternSearch}");
            if (param.getChargePatternSearch() != null) sql.append(" AND QY.SFMS = #{param.chargePatternSearch}");

            sql.append(" AND qy.tzgw = #{ryid}) A ORDER BY RN)");
            sql.append(" WHERE RN > ").append(start).append(" AND RN <= ").append(end);

            return sql.toString();
        }

        // 龙虎榜
        public String getWinnerList(@Param("crmId") String crmId, @Param("queryScope") ApiRequestWinnersListData.QueryScopeEnum queryScope,
                                    @Param("rankingType") ApiRequestWinnersListData.RankingTypeEnum rankingType, @Param("date") String date, @Param("getOneSelf") Boolean getOneSelf) {

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM(\n" +
                    "SELECT A.*, ROWNUM ranking FROM(\n" +
                    "SELECT * FROM(\n" +
                    "SELECT ZB.KHRY,\n" +
                    "ZB.YF,\n" +
                    "ry.ryxm name,\n" +
                    "REPLACE(TRIM(TRANSLATE((SELECT name FROM crmii.lborganization WHERE id= ry.yyb), '0123456789', ' ')), '证券营业部', '') department,\n" +
                    "ry.ryid ryid,");

            switch (rankingType) {
                case MONTHLY_SQLES:     //月金融产品销售量
                    sql.append("round(NVL(SUM(CASE\n" +
                            "WHEN ZB.ZBDM = 'YJRCPJYL_RY' THEN\n" +
                            "ZBZ\n" +
                            "ELSE\n" +
                            "0\n" +
                            "END), 0)) ");
                    break;
                case ANNUAL_SALES:      //年金融产品销售量
                    sql.append("round(NVL(SUM(CASE\n" +
                            "WHEN ZB.ZBDM = 'NJRCPJYL_RY' THEN\n" +
                            "ZBZ\n" +
                            "ELSE\n" +
                            "0\n" +
                            "END), 0)) ");
                    break;
                case ADD_VALID_ACCOUNTS:    //新开净增有效户
                    sql.append("SUM(CASE\n" +
                            "WHEN ZB.ZBDM = 'NXKYXH_RY' THEN\n" +
                            "ZBZ\n" +
                            "ELSE\n" +
                            "0\n" +
                            "END) ");
                    break;
                case NEW_ADD_ASSETS:      //新开净增资产
                    sql.append("round(NVL(SUM(CASE\n" +
                            "WHEN ZB.ZBDM = 'NXKHJZZC_RY' THEN\n" +
                            "ZBZ\n" +
                            "ELSE\n" +
                            "0\n" +
                            "END), 0)) ");
                    break;
                default:
                    break;
            }
            sql.append("reportCard ");

            sql.append("FROM XC_DATA.TJXKHJG ZB, CRM_XC.TRYXX_LS RY\n" +
                    "WHERE ZB.YF = RY.YF\n" +
                    "AND ZB.KHRY = RY.JSRY\n" +
                    "AND ZB.BMLB = 2");

            sql.append(" AND ZB.YF = #{date}");
            if (Objects.equals(queryScope, ApiRequestWinnersListData.QueryScopeEnum.DEPARTMENT) && !StringUtils.isEmpty(crmId)) {
                sql.append(" AND ry.yyb=(select orgid from crmii.tryxx where id = #{crmId})");
            }
            sql.append(" GROUP BY ZB.KHRY, ZB.YF, RY.JSRY,RY.RYXM,RY.YYB,RY.RYID) ORDER BY reportCard DESC )A )");

            // 判断是查询列表还是查询自己
            if (!getOneSelf) sql.append(" WHERE ranking <= 10");
            else sql.append(" WHERE RYID = #{crmId}");

            log.info("getWinnerList-sql: {}", sql.toString());
            return sql.toString();
        }

        // 新股中签提醒
        public String getNewSharestockInfo(@Param("crmId") String crmId, @Param("searchKey") String searchKey, @Param("custLevelSearch") Integer custLevelSearch,
                                           @Param("relationTypeSearch") Integer relationTypeSearch, @Param("startDate") String startDate,
                                           @Param("endDate") String endDate, @Param("start") int start, @Param("end") int end) {

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT *\n" +
                    "  FROM (SELECT A.*, ROWNUM RN\n" +
                    "          FROM (\n" +
                    "SELECT DISTINCT (B.customerId), B.name, B.custLevel, B.phone,\n" +
                            "  B.relationType, B.availableTotal, B.winCode, B.winForShort,\n" +
                            "  B.winCount, B.sharestockAmount, B.winDate, B.winAccount FROM (\n" +
                    "SELECT KH.KHH customerId,\n" +
                    "KH.KHXM name,\n" +
                    "TO_CHAR(KH.SJ) phone,\n" +
                    "ZQ.ZQDM winCode,\n" +
                    "NVL((SELECT ZJYE FROM PUSH_CRM.TKHZHSJ SR WHERE SR.KHH = KH.KHH),0) availableTotal,\n" +
                    "KH.KHJB custLevel,\n" +
                    "ZQ.ZQMC winForShort,\n" +
                    "TO_CHAR(ZQ.ZQSL) winCount,\n" +
                    "TO_CHAR(ZQ.CJJG) sharestockAmount,\n" +
                    "TO_CHAR(TO_DATE(ZQ.ZQRQ, 'YYYYMMDD'), 'YYYY-MM-DD') winDate,\n" +
                    "RY.ID,\n" +
                    "(CASE\n" +
                    "WHEN ZQ.BZ = 1 THEN\n" +
                    "'普通账户'\n" +
                    "WHEN ZQ.BZ = 2 THEN\n" +
                    "'信用账户'\n" +
                    "END) winAccount,\n" +
                    "(select gxlx from CRM_APP.V_KH_RYKHGX khgx where khgx.khh=kh.khh and khgx.ryid=#{crmId} and gxlx=10)||';'||\n" +
                    "(SELECT GXLX FROM CRM_APP.V_KH_RYKHGX KHGX WHERE KHGX.KHH=KH.KHH AND KHGX.RYID=#{crmId} AND GXLX=1)||';'||\n" +
                    "(select gxlx from CRM_APP.V_KH_RYKHGX khgx where khgx.khh=kh.khh and khgx.ryid=#{crmId} and gxlx=2) AS relationType,\n" +
                    "YH.USERID YHID\n" +
                    "FROM CRMII.TKHXX KH\n" +
                    "LEFT JOIN CRMII.TKHGX KHGX \n" +
                    "ON (KHGX.KHH = KH.ID), (SELECT KHH, ZQRQ, ZQDM, ZQMC, ZQSL, CJJG, 1 BZ\n" +
                    "FROM DSC_HIS.T_PSQR_HIS\n" +
                    "UNION ALL\n" +
                    "SELECT KHH, ZQRQ, ZQDM, ZQMC, ZQSL, CJJG, 2 BZ\n" +
                    "FROM DSC_HIS.T_XY_PSQR_HIS) ZQ, CRMII.TRYXX RY, CRMII.TUSER YH\n" +
                    "WHERE ZQ.KHH = KH.KHH\n" +
                    "AND KHGX.RYXX = RY.ID\n" +
                    "AND RY.DYYH = YH.ID\n" +
                    "AND RY.ID = #{crmId}\n" +
                    "AND KHGX.GXLX IN (1, 2, 10)\n" +
                    "AND KHGX.ZT = 0\n" +
                    "AND ZQ.ZQRQ >= #{startDate}\n" +
                    "AND ZQ.ZQRQ <= #{endDate}");

            if (relationTypeSearch != null) sql.append(" AND GXLX = #{relationTypeSearch}");
            if (custLevelSearch != null) sql.append(" AND KHJB = #{custLevelSearch}");

            if (searchKey != null) {
                sql.append("AND (KH.KHH LIKE CONCAT(CONCAT('%', #{searchKey}), '%')\n" +
                        "        OR KH.KHXM LIKE CONCAT(CONCAT('%', #{searchKey}), '%')\n" +
                        "        OR KH.SJ LIKE CONCAT(CONCAT('%', #{searchKey}), '%'))");
            }

            sql.append(" ) B ORDER BY B.winDate DESC,B.winCode, B.customerId ASC\n" +
                    ") A  ORDER BY RN)");
            sql.append(" WHERE  RN > #{start} AND RN <= #{end}");

            log.info("getNewSharestockInfo-sql: {}", sql.toString());
            return sql.toString();
        }

        // 业务机会
        public String getBusChanceInfoPage(@Param("ryid") String ryid, @Param("data") ApiRequestBusChanceData data) {
            String searchKey = data.getSearchKey();
            Integer custLevelSearch = data.getCustLevelSearch();
            Integer relationTypeSearch = data.getRelationTypeSearch();
            ApiRequestBusChanceData.SortByEnum sortBy = data.getSortBy();
            ApiRequestBusChanceData.SortDirectionEnum sortDirection = data.getSortDirection();
            Integer index = data.getIndex();
            Integer pageSize = data.getPageSize();

            if (index == null) index = 0;
            Integer start = index * pageSize;
            Integer end = (index + 1) * pageSize;

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT *\n" +
                    "  FROM (SELECT A.*, ROWNUM RN  FROM (\n" +
                    "SELECT DISTINCT (B.customerId), B.name, B.custLevel, B.riskLevel, B.phone,\n" +
                    "          B.relationType, B.total, B.availableTotal, B.commission, B.turnoverRate,\n" +
                    "          B.totalMarket, B.openDate, B.commissionRate, B.isService FROM (" +
                    " SELECT TO_CHAR(KH.KHH) AS customerId,\n" +
                    "       KH.KHXM AS name,\n" +
                    "       KH.KHJB AS custLevel,\n" +
                    "       KH.FXCSNL AS riskLevel,\n" +
                    "       KH.SJ AS phone,\n" +
                    "       KHGX.RYXM,\n" +
                "           (select gxlx from CRM_APP.V_KH_RYKHGX khgx where khgx.khh=kh.khh and khgx.ryid=#{ryid} and gxlx=10)||';'||\n" +
                "           (SELECT GXLX FROM CRM_APP.V_KH_RYKHGX KHGX WHERE KHGX.KHH=KH.KHH AND KHGX.RYID=#{ryid} AND GXLX=1)||';'||\n" +
                "           (select gxlx from CRM_APP.V_KH_RYKHGX khgx where khgx.khh=kh.khh and khgx.ryid=#{ryid} and gxlx=2) AS relationType,\n" +
                    "       TO_CHAR(NVL(SR.KHZC, 0), '99999999990.00') AS total,\n" +
                    "       TO_CHAR(NVL(SR.ZJYE, 0), '99999999990.00') AS availableTotal,\n" +
                    "       TO_CHAR(NVL(SR.BN_YJ, 0), '99999999990.00') AS commission,\n" +
                    "       TO_CHAR(NVL(SR.ZQSZ, 0), '99999999990.00') AS totalMarket,\n" +
                    "       TO_CHAR(NVL(SR.BN_ZZL, 0), '99999999990.00') AS turnoverRate,\n" +
                    "       TO_CHAR(TO_DATE(KH.KHRQ, 'YYYYMMDD'), 'YYYY-MM-DD') AS openDate,\n" +
                    "       TO_CHAR(NVL(SR.PTZH_BN_JHYJL * 1000, 0), '99999999990.00') AS commissionRate,\n" +
                    "        (SELECT COUNT(1)\n" +
                    "          FROM CRM_APP.TYWJH_GZ GZ\n" +
                    "         WHERE GZ.KHH = KH.KHH\n" +
                    "           AND GZ.RYID = KHGX.RYID) AS isService\n" +
                    "  FROM CRM_APP.V_TKHXX  KH\n" +
                    "  LEFT JOIN PUSH_CRM.TKHZHSJ SR\n" +
                    "    ON KH.KHH = SR.KHH, CRM_APP.V_KH_RYKHGX KHGX\n" +
                    " WHERE KH.KHZT <> 3\n" +
                    "   AND KH.KHH = KHGX.KHH\n" +
                    "   AND KHGX.RYID = #{ryid}\n" +
                    "   AND KHGX.GXLX IN (1, 2, 10)");

            if (!StringUtils.isEmpty(searchKey)) {
                String search = "   AND (KH.KHH LIKE CONCAT(CONCAT('%',#{data.searchKey}), '%')\n" +
                        "        OR KH.KHXM LIKE CONCAT(CONCAT('%',#{data.searchKey}), '%')\n" +
                        "        OR KH.SJ LIKE CONCAT(CONCAT('%',#{data.searchKey}), '%'))";
                sql.append(search);
            }

            if (custLevelSearch != null) sql.append(" AND KH.KHJB = #{data.custLevelSearch}");
            if (relationTypeSearch != null) sql.append(" AND KHGX.GXLX = #{data.relationTypeSearch}");

            sql.append(" AND KH.KHH IN (SELECT KHH FROM CRM_APP.TKHYWQX WHERE ");

            // 业务类型：融资融券、个股期权、沪港通、深港通、创业板、OTC、科创版
            switch (data.getBusType()) {
                case RZRQ:
                    sql.append(" rzrq = ");
                    break;
                case GGQQ:
                    sql.append(" ggqq = ");
                    break;
                case HGT:
                    sql.append(" hgt = ");
                    break;
                case SGT:
                    sql.append(" sgt = ");
                    break;
                case CYB:
                    sql.append(" cyb = ");
                    break;
                case OTC:
                    sql.append(" otc = ");
                    break;
                case KCB:
                    sql.append(" kcb = ");
                    break;
                default:
                    break;
            }

            // 开通/ 未开通:为1 代表已开通，为0代表满足条件未开通,
            if (data.isIsOpen()) sql.append("1)) B");
            else sql.append("0)) B");

            // 排序：总资产、资金余额、周转率、开户日期
            sql.append(" ORDER BY ");
            if (sortBy != null && sortDirection != null) {
                switch (data.getSortBy()) {
                    case TOTAL_ASSET:
                        sql.append("total");
                        break;
                    case AVAILABLE_ASSET:
                        sql.append("availableTotal");
                        break;
                    case TURNOVER_RATE:
                        sql.append("turnoverRate");
                        break;
                    case OPEN_DATE:
                        sql.append("openDate");
                        break;
                    default:
                        break;
                }
                if (Objects.equals(data.getSortDirection(), ApiRequestBusChanceData.SortDirectionEnum.ASC))
                    sql.append(" ASC");
                else
                    sql.append(" DESC");
            } else {
                sql.append("total DESC");
            }

            sql.append(" ) A");
            // 是否服务（已服务/未服务）
            if (data.isIsServiceSearch() != null) {
                sql.append(" WHERE isService ");
                if (data.isIsServiceSearch())
                    sql.append("> 0");
                else
                    sql.append("= 0");
            }

            sql.append(" ORDER BY RN) WHERE RN > ").append(start).append(" AND RN <= ").append(end);
            log.debug("getBusChanceInfoPage-sql : " + sql.toString());
            return sql.toString();
        }


        public String getNewOpenCustomersPage(@Param(value = "accountId") Integer accountId, @Param("param") ApiRequestNewOpenCustomersPageData param) {
            Integer pageSize = null;
            Integer index = null;
            ApiRequestNewOpenCustomersPageData.SortByEnum sortBy = null;
            ApiRequestNewOpenCustomersPageData.SortDirectionEnum sortDirection=null;
            if (param != null) {
                pageSize = param.getPageSize();
                index=param.getIndex();
                sortBy = param.getSortBy();
                sortDirection = param.getSortDirection();
            }

            if (pageSize == null || pageSize <= 0) {
                pageSize = 20;
                param.setPageSize(pageSize);
            }
            if (index == null || index <= 0) {
                index = 1;
                param.setIndex(index);
            }else{
                param.setIndex(++index);
            }

            int page=(param.getIndex()-1)*param.getPageSize();
            int rows=param.getIndex()*param.getPageSize()+1;


            if (sortBy == null) {
                sortBy = ApiRequestNewOpenCustomersPageData.SortByEnum.DEFAULT;
            }
            if (sortDirection == null) {
                sortDirection = ApiRequestNewOpenCustomersPageData.SortDirectionEnum.DESC;
            }

            String sortColumn;
            switch (sortBy) {
                default:
                    sortColumn = "customerId ";
                    break;
            }

            String direction = sortDirection.name();
            String filter = "";
            NewOpenCustomerFilter customerFilter = param.getFilter();
            StringBuffer stringBuffer=new StringBuffer();

            if (customerFilter != null) {

                String tradeNum=customerFilter.getTradeNum();
                if(!StringUtils.isEmpty(tradeNum)){
                    switch (tradeNum) {
                    case "1" :
                        filter += " AND DM.JYL < 10000 ";
                        break;
                    case "2" :
                        filter += " AND DM.JYL >= 10000 AND DM.JYL < 100000 ";
                        break;
                    case "3" :
                        filter += " AND DM.JYL >= 100000 AND DM.JYL < 500000 ";
                        break;
                    case "4" :
                        filter += " AND DM.JYL >= 500000 ";
                        break;
                    default:
                        break;
                    }
                }

                String capitalIncome=customerFilter.getCapitalIncome();
                if(!StringUtils.isEmpty(capitalIncome)){
                    switch (capitalIncome) {
                        case "1" :
                            filter += " AND DM.ZCJLR < 10000 ";
                            break;
                        case "2" :
                            filter += " AND DM.ZCJLR >= 10000 AND DM.ZCJLR < 100000 ";
                            break;
                        case "3" :
                            filter += " AND DM.ZCJLR >= 100000 AND DM.ZCJLR < 500000 ";
                            break;
                        case "4" :
                            filter += " AND DM.ZCJLR >= 500000 ";
                            break;
                        default:
                            break;
                    }
                }

                String isIncome=customerFilter.getIsIncome();
                String isTrade=customerFilter.getIsTrade();
                if(!StringUtils.isEmpty(isIncome)&&!StringUtils.isEmpty(isTrade)){
                    if(isIncome.equals("0")&&isTrade.equals("0")){
                        //未入金未交易
                        filter += "  AND  DM.CRJE = 0 AND  DM.JYJ = 0  ";
                    }else  if(isIncome.equals("1")&&isTrade.equals("0")){
                        //入金未交易
                        filter += "  AND  DM.CRJE > 0 AND  DM.JYJ = 0  ";
                    } if(isIncome.equals("1")&&isTrade.equals("1")){
                        //入金已经交易
                        filter += "  AND  DM.JYJ > 0  ";
                    }
                }

                String search=customerFilter.getSearch();
                if(!StringUtils.isEmpty(search)){
                    filter += " AND ( TKHXX.KHH like "+"'%"+search+"%' OR TKHXX.SJ like "+"'%"+search+"%' OR  TKHXX.KHXM  like  "+"'%"+search+"%') ";
                }
                String isStore=customerFilter.getIsStore();
                if(!StringUtils.isEmpty(isStore)){
                    filter+=" AND (SELECT COUNT(1) FROM CRM_APP.TXKKH_GZ GZ  WHERE GZ.KHH = TKHXX.KHH  AND GZ.RYID = GX.RYID) = ";
                    if(isStore.equals("1")){
                        //收藏
                        filter += " '1' ";
                    }else{
                        filter += " '0' ";
                    }
                }
            }

            if(!StringUtils.isEmpty(accountId)){
                filter += "  AND GX.RYID ="+"'"+accountId+"'";
            }
            String sql = "select * from (\n" +
                    "select rownum as rn,a.* from (\n" +
                    "SELECT distinct(TKHXX.KHH) as customerId , TKHXX.KHXM  as name ,TKHXX.SJ as phone,DM.CRJE,TKHXX.KHJB  as customerLevel,TKHXX.FXCSNL as riskLevel ,GX.GXLX as relationType ,TO_CHAR(TO_DATE(TKHXX.KHRQ, 'YYYY-MM-DD'), 'YYYY-MM-DD') as openDate ," +
                    " TO_CHAR(NVL((SELECT khzc FROM PUSH_CRM.TKHZHSJ T WHERE KHH =TKHXX.KHH), 0), '99999999990.00') as total ," +
                    " TO_CHAR(NVL((SELECT zqsz FROM PUSH_CRM.TKHZHSJ T WHERE KHH =TKHXX.KHH), 0), '99999999990.00') as totalMarket  ," +
                    " TO_CHAR(NVL(DM.ZCJLR, 0), '99999999990.00') as netInflow, " +
                    " TO_CHAR(NVL(DM.JYL, 0), '99999999990.00') as tradeNum, " +
                    " TO_CHAR(NVL(DM.JYJ, 0), '99999999990.00') as netCommission, " +
                    " (SELECT COUNT(1) FROM CRM_APP.TXKKH_GZ GZ  WHERE GZ.KHH = TKHXX.KHH AND GZ.RYID = GX.RYID)  as isStore,NVL(DM.CRJE,0) as isIncome " +
                    " FROM DM.T_STAT_KHH_JBN DM, CRM_APP.V_TKHXX TKHXX, CRM_APP.V_KH_RYKHGX GX  " +
                    " WHERE DM.KHH = TKHXX.KHH  AND TKHXX.KHH = GX.KHH  AND GX.GXLX = 10  AND KHRQ BETWEEN TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE, -6), 'YYYYMMDD')) AND TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))  " +
                    "  " + filter + "\n";
                sql += "order by " + sortColumn + " " + direction + " ) a  where rownum < "+rows+"  ) b where  rn> "+page;
            return sql;
        }


        public String getCustomersPage(@Param(value = "isDepartment") boolean isDepartment, @Param(value = "accountId") Integer accountId, @Param("param") ApiRequestCustomersPageData param) {
            Integer pageSize = null;
            Integer index = null;
            ApiRequestCustomersPageData.SortByEnum sortBy = null;
            ApiRequestCustomersPageData.SortDirectionEnum sortDirection = null;

            if (param != null) {
                pageSize = param.getPageSize();
                index=param.getIndex();
                sortBy = param.getSortBy();
                sortDirection = param.getSortDirection();
            }

            if (pageSize == null || pageSize <= 0) {
                pageSize = 20;
                param.setPageSize(pageSize);
            }


            if (index == null || index <= 0) {
                index = 1;
                param.setIndex(index);
            }else{
                param.setIndex(++index);
            }
            int page=(param.getIndex()-1)*param.getPageSize();
            int rows=param.getIndex()*param.getPageSize()+1;

            if (sortBy == null) {
                sortBy = ApiRequestCustomersPageData.SortByEnum.DEFAULT;
            }
            if (sortDirection == null) {
                sortDirection = ApiRequestCustomersPageData.SortDirectionEnum.DESC;
            }
            String sortColumn;
            switch (sortBy) {
                case TOTAL:
                    sortColumn = "total ";
                    break;
                case AVAILABLE_TOTAL:
                    sortColumn = "availableTotal ";
                    break;
                case TURNOVER_RATE:
                    sortColumn = "turnoverRate ";
                    break;
                case OPER_DATE:
                    sortColumn = "openDate ";
                    break;
                default:
                    sortColumn = "customerId ";
                    break;
            }
            String direction = sortDirection.name();
            String filter = "";
            String filterL2 = "";
            CustomerFilter customerFilter = param.getFilter();
            StringBuffer stringBuffer=new StringBuffer();

            if (customerFilter != null) {
                String riskLevel=customerFilter.getRiskLevel();
                if(!StringUtils.isEmpty(riskLevel)){
                    filter += " and  TKHXX.FXCSNL =  "+"'"+riskLevel+"'";
                }
                String customerLevel=customerFilter.getCustomerLevel();
                if(!StringUtils.isEmpty(customerLevel)){
                    filter += " and  TKHXX.KHJB =  "+"'"+customerLevel+"'";
                }
                String relationType=customerFilter.getRelationType();
                if(!StringUtils.isEmpty(relationType)){
                    filter += " and  GX.GXLX =  "+"'"+relationType+"'";
                }

                String search=customerFilter.getSearch();
                if(!StringUtils.isEmpty(search)){
                    filter += " AND ( TKHXX.KHH like "+"'%"+search+"%' OR TKHXX.SJ like "+"'%"+search+"%' OR  TKHXX.KHXM  like  "+"'%"+search+"%') ";
                }

                String isStore=customerFilter.getIsStore();
                if(!StringUtils.isEmpty(isStore)){
                    filter+=" AND (SELECT COUNT(1) FROM CRM_APP.TKHLB_GZ GZ  WHERE GZ.KHH = TKHXX.KHH  AND GZ.RYID = GX.RYID) = ";
                    if(isStore.equals("1")){
                        //收藏
                        filter += " '1' ";
                    }else{
                        filter += " '0' ";
                    }
                }

                String isActive = customerFilter.getIsActive();
                if (!StringUtils.isEmpty(isActive)) {
                    if (isActive.equals("1")) {
                        //已激活炼金术
                        filterL2 += " AND isActive > 0 ";
                    } else {
                        //未激活炼金术
                        filterL2 += " AND isActive = 0 ";
                    }
                }

                List<CustomerFilterCustomerList> customerLists=customerFilter.getCustomerList();
                if(customerLists!=null&&customerLists.size()>0){
                    stringBuffer.setLength(0);
                    stringBuffer.append("(");
                    customerLists.stream().forEach(customerId->{
                        stringBuffer.append(customerId.getCustomerId()).append(",");
                    });
                    stringBuffer.deleteCharAt(stringBuffer.length() - 1);
                    stringBuffer.append(")");
                    filter += " and TKHXX.KHH in "+stringBuffer.toString();
                }

                CustomerFilterAsset asset = customerFilter.getAsset();
                if (asset != null) {
                    AssetRange total = asset.getTotal();
                    AssetRange totalMarket= asset.getTotalMarket();
                    if (total != null) {
                        if (total.getMin() != null) {
                            filter += " and SR.KHZC>=#{param.filter.asset.total.min}";
                        }
                        if (total.getMax() != null) {
                            filter += " and SR.KHZC<=#{param.filter.asset.total.max}";
                        }
                    }

                    if (totalMarket != null) {
                        if (totalMarket.getMin() != null) {
                            filter += " and SR.ZJYE>=#{param.filter.asset.totalMarket.min}";
                        }
                        if (totalMarket.getMax() != null) {
                            filter += " and SR.ZJYE<=#{param.filter.asset.totalMarket.max}";
                        }
                    }
                }
            }

            if(!StringUtils.isEmpty(accountId)){
                if (!isDepartment) {
                    filter += " and GX.RYID ="+"'"+accountId+"'";
                } else {
                    filter += " and TKHXX.yyb=(SELECT orgid FROM CRMII.tryxx WHERE ID = #{accountId})";
                }
            }

            String sql;
            if (!isDepartment) {    // 客户全景
                sql = "select * from (\n" +
                        "select rownum as rn,a.* from (select * from (\n" +
                        "SELECT distinct(TO_CHAR(TKHXX.KHH))  as customerId , TKHXX.KHXM  as name ,TKHXX.KHJB  as customerLevel  ,TKHXX.FXCSNL as riskLevel ,TKHXX.FXCSNL FXCSNL,TKHXX.SJ as phone ," +
                        " to_number(TO_CHAR(NVL(SR.KHZC, 0), '99999999990.00')) as total ," +
                        " to_number(TO_CHAR(NVL(SR.ZJYE, 0), '99999999990.00')) as availableTotal, " +
                        " TO_CHAR(NVL(SR.BN_YJ, 0), '99999999990.00') as commission, " +
                        " TO_CHAR(NVL(SR.ZQSZ, 0), '99999999990.00') as totalMarket, " +
                        " TO_CHAR(NVL(SR.BN_ZZL, 0), '99999999990.00') as turnoverRate , " +
                        " TO_CHAR(TO_DATE(TKHXX.KHRQ, 'YYYY-MM-DD'), 'YYYY-MM-DD') as openDate ," +
                        " TO_CHAR(NVL(SR.PTZH_BN_JHYJL*1000, 0), '99999999990.00') as commissionRate, " +
                        " (SELECT COUNT(1) FROM CRM_APP.TKHLB_GZ GZ WHERE GZ.KHH = TKHXX.KHH  AND GZ.RYID = GX.RYID) as isStore, " +
                        " (SELECT COUNT(1) FROM CRMII.STATS_FUND_ACCT_INFO JH, DSC_BAS.T_ZJZH ZJZH WHERE JH.FUND_ACCT = ZJZH.ZJZH AND (JH.IS_ACTIVE = '1' or JH.IS_ACTIVE = 'Y') AND ZJZH.KHH = TKHXX.KHH ) AS isActive " +
                        " FROM CRM_APP.V_TKHXX TKHXX  " +
                        " LEFT JOIN PUSH_CRM.TKHZHSJ SR  ON TKHXX.KHH = SR.KHH, CRM_APP.V_KH_RYKHGX GX " +
                        " WHERE TKHXX.KHH = GX.KHH  AND GX.GXLX in ('1','2','10')  " + filter + ") where 1=1 " + filterL2 + " \n";
                sql += "order by " + sortColumn + " " + direction + " ) a where rownum < "+rows+"  ) b where  rn> "+page;
            } else {    // 营业部客户列表
                sql = "select * from (\n" +
                        "select rownum as rn,a.* from (\n" +
                        "SELECT distinct(TO_CHAR(TKHXX.KHH))  as customerId , TKHXX.KHXM  as name ,TKHXX.KHJB  as customerLevel  ,TKHXX.FXCSNL as riskLevel ,TKHXX.FXCSNL FXCSNL,TKHXX.SJ as phone ," +
                        " TO_CHAR(NVL(SR.KHZC, 0), '99999999990.00') as total ," +
                        " TO_CHAR(NVL(SR.ZJYE, 0), '99999999990.00') as availableTotal, " +
                        " TO_CHAR(NVL(SR.BN_YJ, 0), '99999999990.00') as commission, " +
                        " TO_CHAR(NVL(SR.ZQSZ, 0), '99999999990.00') as totalMarket, " +
                        " TO_CHAR(NVL(SR.BN_ZZL, 0), '99999999990.00') as turnoverRate , " +
                        " TO_CHAR(TO_DATE(TKHXX.KHRQ, 'YYYY-MM-DD'), 'YYYY-MM-DD') as openDate ," +
                        " TO_CHAR(NVL(SR.PTZH_BN_JHYJL*1000, 0), '99999999990.00') as commissionRate " +
                        " FROM CRM_APP.V_TKHXX TKHXX  " +
                        " LEFT JOIN PUSH_CRM.TKHZHSJ SR  ON TKHXX.KHH = SR.KHH " +
                        " WHERE 1 = 1 " + filter + "\n";
                sql += "order by " + sortColumn + " " + direction + " ) a where rownum < "+rows+"  ) b where  rn> "+page;
            }
            return sql;
        }
    }
}
